﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using Model;
using System.Data;

namespace Helpers
{
    public class DAL
    {
        private const string CATEGORIES_TABLE = "categories";
        public static string connectionString = string.Empty;
        private static SQLiteConnection dbConnection = null;
        private const string FILES_TABLE = "files";
        private const string MESSAGES_TABLE = "messages";
        private const string USERS_TABLE = "users";

        public static bool isConnected
        {
            get
            {
                return ((dbConnection != null) && (dbConnection.State == ConnectionState.Open));
            }
        }

        public static void AddMessage(MessageItem message)
        {
            if (!isConnected)
            {
                Init();
            }
            SQLiteCommand command = dbConnection.CreateCommand();
            command.Parameters.AddWithValue(":author_name", message.AuthorName);
            command.Parameters.AddWithValue(":message_text", message.MessageText);
            command.Parameters.AddWithValue(":date", message.Date);
            command.Parameters.AddWithValue(":ip", message.Ip);
            command.CommandText = "INSERT INTO messages(author_name, message_text, date, ip) VALUES (:author_name, :message_text, :date, :ip)";
            command.ExecuteNonQuery();
        }

        public static void Close()
        {
            if (isConnected)
            {
                dbConnection.Close();
            }
        }

        public static void CreateCategory(string categoryName)
        {
            if (!isConnected)
            {
                Init();
            }
            SQLiteCommand command = dbConnection.CreateCommand();
            command.Parameters.AddWithValue(":name", categoryName);
            command.CommandText = "INSERT INTO categories(name) VALUES (:name)";
            command.ExecuteNonQuery();
        }

        public static void DeleteCategory(int categoryID)
        {
            if (!isConnected)
            {
                Init();
            }
            SQLiteCommand command = dbConnection.CreateCommand();
            command.Parameters.AddWithValue(":id", categoryID);
            command.CommandText = "DELETE FROM categories WHERE id=:id";
            command.ExecuteNonQuery();
        }

        public static void DeleteFile(int fileID)
        {
            if (!isConnected)
            {
                Init();
            }
            SQLiteCommand command = dbConnection.CreateCommand();
            command.Parameters.AddWithValue(":id", fileID);
            command.CommandText = "DELETE FROM files WHERE id=:id";
            command.ExecuteNonQuery();
        }

        public static void DeleteAllFiles()
        {
            if (!isConnected)
            {
                Init();
            }
            SQLiteCommand command = dbConnection.CreateCommand();
            command.CommandText = "DELETE FROM files";
            command.ExecuteNonQuery();
        }

        public static void DeleteMessage(int ID)
        {
            if (!isConnected)
            {
                Init();
            }
            SQLiteCommand command = dbConnection.CreateCommand();
            command.Parameters.AddWithValue(":id", ID);
            command.CommandText = "DELETE FROM messages WHERE id=:id";
            command.ExecuteNonQuery();
        }

        public static void DeleteAllMessages()
        {
            if (!isConnected)
            {
                Init();
            }
            SQLiteCommand command = dbConnection.CreateCommand();
            command.CommandText = "DELETE FROM messages";
            command.ExecuteNonQuery();
        }

        public static void EditCategory(CategoryItem category)
        {
            if (!isConnected)
            {
                Init();
            }
            SQLiteCommand command = dbConnection.CreateCommand();
            command.Parameters.AddWithValue(":id", category.ID);
            command.Parameters.AddWithValue(":name", category.Name);
            command.CommandText = "UPDATE categories SET name=:name WHERE id=:id";
            command.ExecuteNonQuery();
        }

        public static void EditFile(FileItem file)
        {
            if (!isConnected)
            {
                Init();
            }
            SQLiteCommand command = dbConnection.CreateCommand();
            command.Parameters.AddWithValue(":id", file.ID);
            command.Parameters.AddWithValue(":name", file.Name);
            command.Parameters.AddWithValue(":description", file.Description);
            command.Parameters.AddWithValue(":ip", file.Ip);
            command.Parameters.AddWithValue(":date", file.Date);
            command.Parameters.AddWithValue(":category_id", file.CategoryID);
            command.Parameters.AddWithValue(":down_count", file.DownCount);
            command.Parameters.AddWithValue(":path", file.Path);
            command.CommandText = "UPDATE files SET name=:name, description=:description, ip=:ip, date=:date, category_id=:category_id, down_count=:down_count, path=:path WHERE id=:id";
            command.ExecuteNonQuery();
        }

        public static void EditMessage(MessageItem message)
        {
            if (!isConnected)
            {
                Init();
            }
            SQLiteCommand command = dbConnection.CreateCommand();
            command.Parameters.AddWithValue(":id", message.ID);
            command.Parameters.AddWithValue(":author_name", message.AuthorName);
            command.Parameters.AddWithValue(":message_text", message.MessageText);
            command.Parameters.AddWithValue(":date", message.Date);
            command.Parameters.AddWithValue(":ip", message.Ip);
            command.CommandText = "UPDATE messages SET author_name=:author_name, message_text=:message_text, date=:date, ip=:ip WHERE id=:id";
            command.ExecuteNonQuery();
        }

        public static List<CategoryItem> GetCategories()
        {
            if (!isConnected)
            {
                Init();
            }
            //SQLiteCommand command = dbConnection.CreateCommand();
            //command.CommandText = "SELECT COUNT(*) FROM files WHERE category_id=:category_id";
            //command.Parameters.Add(new SQLiteParameter(":category_id"));
            SQLiteCommand command = dbConnection.CreateCommand();
            command.CommandText = "SELECT * FROM categories";
            SQLiteDataReader reader = command.ExecuteReader();
            List<CategoryItem> list = new List<CategoryItem>();
            while (reader.Read())
            {
                CategoryItem item = new CategoryItem();
                item.ID = Convert.ToInt32(reader["id"]);
                item.Name = (string)reader["name"];
                //command.Parameters[":category_id"].Value = reader["id"];
                item.CountFiles = GetCategoryCountFiles(item.ID); //Convert.ToInt32(command.ExecuteScalar());
                list.Add(item);
            }
            return list;
        }

        public static int GetCategoryCountFiles(int ID)
        {
            if (!isConnected)
            {
                Init();
            }
            SQLiteCommand command = dbConnection.CreateCommand();
            command.Parameters.AddWithValue(":id", ID);
            command.CommandText = "SELECT COUNT(*) FROM files WHERE category_id=:id";
            return Convert.ToInt32(command.ExecuteScalar());
        }

        public static string GetCategoryName(int ID)
        {
            if (!isConnected)
            {
                Init();
            }
            SQLiteCommand command = dbConnection.CreateCommand();
            command.Parameters.AddWithValue(":id", ID);
            command.CommandText = "SELECT * FROM categories WHERE id=:id";
            return command.ExecuteReader()["name"].ToString();
        }

        public static FileItem GetFile(int fileID)
        {
            if (!isConnected)
            {
                Init();
            }
            SQLiteCommand command = dbConnection.CreateCommand();
            command.Parameters.AddWithValue(":id", fileID);
            command.CommandText = "SELECT * FROM files WHERE id=:id";
            SQLiteDataReader reader = command.ExecuteReader();
            FileItem item = new FileItem();
            item.ID = Convert.ToInt32(reader["id"]);
            item.Name = (string)reader["name"];
            item.Description = (string)reader["description"];
            item.Ip = (string)reader["ip"];
            item.Date = (DateTime)reader["date"];
            item.CategoryID = Convert.ToInt32(reader["category_id"]);
            item.DownCount = Convert.ToInt32(reader["down_count"]);
            item.Path = (string)reader["path"];
            return item;
        }

        public static List<FileItem> GetFilesList()
        {
            if (!isConnected)
            {
                Init();
            }
            SQLiteCommand command = dbConnection.CreateCommand();
            command.CommandText = "SELECT * FROM files ORDER BY id DESC";
            SQLiteDataReader reader = command.ExecuteReader();
            List<FileItem> list = new List<FileItem>();
            while (reader.Read())
            {
                FileItem item = new FileItem();
                item.ID = Convert.ToInt32(reader["id"]);
                item.Name = (string)reader["name"];
                item.Description = (string)reader["description"];
                item.Ip = (string)reader["ip"];
                item.Date = (DateTime)reader["date"];
                item.CategoryID = Convert.ToInt32(reader["category_id"]);
                item.DownCount = Convert.ToInt32(reader["down_count"]);
                item.Path = (string)reader["path"];
                list.Add(item);
            }
            return list;
        }

        public static List<FileItem> GetFilesList(int categoryID)
        {
            if (!isConnected)
            {
                Init();
            }
            SQLiteCommand command = dbConnection.CreateCommand();
            command.Parameters.AddWithValue(":category_id", categoryID);
            command.CommandText = "SELECT * FROM files WHERE category_id=:category_id ORDER BY id DESC";
            SQLiteDataReader reader = command.ExecuteReader();
            List<FileItem> list = new List<FileItem>();
            while (reader.Read())
            {
                FileItem item = new FileItem();
                item.ID = Convert.ToInt32(reader["id"]);
                item.Name = (string)reader["name"];
                item.Description = (string)reader["description"];
                item.Ip = (string)reader["ip"];
                item.Date = (DateTime)reader["date"];
                item.CategoryID = Convert.ToInt32(reader["category_id"]);
                item.DownCount = Convert.ToInt32(reader["down_count"]);
                item.Path = (string)reader["path"];
                list.Add(item);
            }
            return list;
        }

        public static List<FileItem> GetLastFiles(int filesCount)
        {
            if (!isConnected)
            {
                Init();
            }
            SQLiteCommand command = dbConnection.CreateCommand();
            command.CommandText = "SELECT * FROM files ORDER BY ID DESC LIMIT " + filesCount.ToString();
            SQLiteDataReader reader = command.ExecuteReader();
            List<FileItem> list = new List<FileItem>();
            while (reader.Read())
            {
                FileItem item = new FileItem();
                item.ID = Convert.ToInt32(reader["id"]);
                item.Name = (string)reader["name"];
                item.Description = (string)reader["description"];
                item.Ip = (string)reader["ip"];
                item.Date = (DateTime)reader["date"];
                item.CategoryID = Convert.ToInt32(reader["category_id"]);
                item.DownCount = Convert.ToInt32(reader["down_count"]);
                item.Path = (string)reader["path"];
                list.Add(item);
            }
            return list;
        }

        public static MessageItem GetMessage(int ID)
        {
            if (!isConnected)
            {
                Init();
            }
            SQLiteCommand command = dbConnection.CreateCommand();
            command.Parameters.AddWithValue(":id", ID);
            command.CommandText = "SELECT * FROM messages WHERE id=:id";
            SQLiteDataReader reader = command.ExecuteReader();
            MessageItem item = new MessageItem();
            item.ID = ID;
            item.AuthorName = (string)reader["author_name"];
            item.MessageText = (string)reader["message_text"];
            item.Date = (DateTime)reader["date"];
            item.Ip = (string)reader["ip"];
            return item;
        }

        public static List<MessageItem> GetMessagesList()
        {
            if (!isConnected)
            {
                Init();
            }
            SQLiteCommand command = dbConnection.CreateCommand();
            command.CommandText = "SELECT * FROM messages ORDER BY id DESC";
            SQLiteDataReader reader = command.ExecuteReader();
            List<MessageItem> list = new List<MessageItem>();
            while (reader.Read())
            {
                MessageItem item = new MessageItem();
                item.ID = Convert.ToInt32(reader["id"]);
                item.AuthorName = (string)reader["author_name"];
                item.MessageText = (string)reader["message_text"];
                item.Date = (DateTime)reader["date"];
                item.Ip = (string)reader["ip"];
                list.Add(item);
            }
            return list;
        }

        private static void Init()
        {
            dbConnection = new SQLiteConnection(connectionString);
            dbConnection.Open();
        }

        public static bool Login(string login, string password)
        {
            if (!isConnected)
            {
                Init();
            }
            SQLiteCommand command = dbConnection.CreateCommand();
            command.Parameters.AddWithValue(":login", login);
            command.Parameters.AddWithValue(":password", password);
            command.CommandText = "SELECT COUNT(*) FROM users WHERE (login=:login AND password=:password)";
            return (Convert.ToInt32(command.ExecuteScalar()) == 1);
        }

        public static int SaveFile(FileItem file)
        {
            if (!isConnected)
            {
                Init();
            }
            SQLiteCommand command = dbConnection.CreateCommand();
            command.Parameters.AddWithValue(":name", file.Name);
            command.Parameters.AddWithValue(":description", file.Description);
            command.Parameters.AddWithValue(":ip", file.Ip);
            command.Parameters.AddWithValue(":date", file.Date);
            command.Parameters.AddWithValue(":category_id", file.CategoryID);
            command.Parameters.AddWithValue(":down_count", file.DownCount);
            command.Parameters.AddWithValue(":path", file.Path);
            command.CommandText = "INSERT INTO files(name, description, ip, date, category_id, down_count, path) VALUES (:name, :description, :ip, :date, :category_id, :down_count, :path)";
            command.ExecuteNonQuery();
            command.CommandText = "SELECT last_insert_rowid()";
            return Convert.ToInt32(command.ExecuteScalar());
        }
    }
}
